package org.perso.bd.resource;

import org.perso.bd.CollectionBuilder;
import org.perso.bd.bean.Bd;
import org.perso.bd.bean.Collection;
import org.perso.bd.bean.Serie;
import org.restlet.resource.Get;
import org.restlet.resource.ServerResource;

/**
 * Created by b.bassac on 20/10/2014.
 */
public class ListingSQLResource extends ServerResource {

    @Get("json")
    public String getCollection() throws Exception {

        Collection c = CollectionBuilder.getCollection();
        String result = displaySql(c);
        return result;

    }

    private String displaySql(Collection c) {
        StringBuilder builder = new StringBuilder();
        builder.append("CREATE TABLE IF NOT EXISTS `Bds` (\n" +
                "  `id` int(11) NOT NULL,\n" +
                "  `titre` text collate latin1_general_ci NOT NULL,\n" +
                "  `numero` text collate latin1_general_ci NOT NULL,\n" +
                "  `couvertureUrl` text collate latin1_general_ci NOT NULL,\n" +
                "  `possedee` int(11) NOT NULL,\n" +
                "  `serie_id` int(11) NOT NULL,\n" +
                "  PRIMARY KEY  (`id`),\n" +
                "  KEY `SeriesFK` (`serie_id`)\n" +
                ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;");
        builder.append("\n");
        builder.append("CREATE TABLE IF NOT EXISTS `Series` (\n" +
                "  `id` int(11) NOT NULL,\n" +
                "  `nom` text collate latin1_general_ci NOT NULL,\n" +
                "  `fini` tinyint(4) NOT NULL,\n" +
                "  `imageUrl` text collate latin1_general_ci NOT NULL,\n" +
                "  `editeur` text collate latin1_general_ci NOT NULL,\n" +
                "  PRIMARY KEY  (`id`)\n" +
                ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;");
        builder.append("\n");
        for(Serie s : c.getListeSerie()){
            builder.append("INSERT INTO  `lioncorps`.`Series` (" +
                    "`id` ," +
                    "`nom` ," +
                    "`fini` ," +
                    "`imageUrl` ," +
                    "`editeur`" +
                    ")" +
                    "VALUES (" +
                    "'" + s.getId() + "',  " +
                    "'" + convertWithEscape(s.getNom()) + "'," +
                    " " + getFini(s.isFini()) + "," +
                    "  '" + s.getImageUrl() + "'," +
                    "  '" + convertWithEscape(s.getEditeur()) + "'" +
                    ");");
            builder.append("\n");
                    for(Bd possedee : s.getListPossede()){
                        builder.append("INSERT INTO  `lioncorps`.`Bds` (" +
                                "`id` ," +
                                "`titre` ," +
                                "`numero` ," +
                                "`couvertureUrl` ," +
                                "`possedee` ," +
                                "`serie_id`" +
                                ")" +
                                "VALUES (" +
                                possedee.getId() + "," +
                                "  '" + convertWithEscape(possedee.getTitre()) + "'," +
                                " '" + convertWithEscape(possedee.getNumero()) + "'," +
                                "  '" + possedee.getCouvertureUrl() + "'," +
                                "  '1'," +
                                "  '" + s.getId() + "'" +
                                ");");
                        builder.append("\n");
                    }
                    for(Bd possedee : s.getListManquante()){
                        builder.append("INSERT INTO  `lioncorps`.`Bds` (" +
                                "`id` ," +
                                "`titre` ," +
                                "`numero` ," +
                                "`couvertureUrl` ," +
                                "`possedee` ," +
                                "`serie_id`" +
                                ")" +
                                "VALUES (" +
                                possedee.getId() + "," +
                                "  '" + convertWithEscape(possedee.getTitre()) + "'," +
                                " '" + convertWithEscape(possedee.getNumero()) + "'," +
                                "  '" + possedee.getCouvertureUrl() + "'," +
                                "  '0'," +
                                "  '" + s.getId() + "'" +
                                ");");
                        builder.append("\n");
                    }


        }
        return builder.toString();
    }

    private String convertWithEscape(String s){
        return s.replace("'","''");
    }

    private int getFini(boolean fini) {
        return fini ? 1 : 0;
    }
}
